import pandas as pd

CSV_PATH = "att_2025-05-24_converted(in).csv" 

df = pd.read_csv(CSV_PATH)

df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")

# Drop the spacer/junk rows (keep only real location records)
df_clean = df.dropna(subset=["device_lat", "device_lon"]).copy()

df_clean["row_id"] = pd.to_numeric(df_clean["row_id"], errors="coerce")
df_clean = df_clean.dropna(subset=["row_id"]).copy()

df_clean.reset_index(drop=True, inplace=True)

print("Raw rows:", len(df))
print("Clean rows:", len(df_clean))
df_clean.head(272)

Raw rows: 544
Clean rows: 272

	row_id	conn_date	conn_time_utc	timing_advance	cell_id	network_type	tower_lat	tower_lon	sector_orientation	device_lat	device_lon
0	318.0	5/24/2025	0:04:03	3.0	18497943.0	4G	39.343300	-76.729202	240.0	39.343746	-76.731483
1	319.0	5/24/2025	0:04:18	3.0	18497802.0	4G	39.343300	-76.729202	240.0	39.344002	-76.731606
2	320.0	5/24/2025	0:04:41	3.0	18497943.0	4G	39.343300	-76.729202	240.0	39.343746	-76.731483
3	321.0	5/24/2025	0:05:08	3.0	18497802.0	4G	39.343300	-76.729202	240.0	39.344002	-76.731606
4	322.0	5/24/2025	0:06:21	3.0	18497943.0	4G	39.343300	-76.729202	240.0	39.343746	-76.731483
...	...	...	...	...	...	...	...	...	...	...	...
267	585.0	5/24/2025	23:55:17	13.0	18422808.0	4G	39.392778	-76.789167	240.0	39.391052	-76.799850
268	586.0	5/24/2025	23:55:54	13.0	18422794.0	4G	39.392778	-76.789167	240.0	39.384132	-76.788139
269	587.0	5/24/2025	23:56:21	13.0	18423008.0	4G	39.392778	-76.789167	240.0	39.390850	-76.799789
270	588.0	5/24/2025	23:57:15	14.0	18422808.0	4G	39.392778	-76.789167	240.0	39.391052	-76.799850
271	589.0	5/24/2025	23:58:14	14.0	18422794.0	4G	39.392778	-76.789167	240.0	39.384132	-76.788139

272 rows × 11 columns

import pandas as pd
import plotly.express as px
import plotly.io as pio

pio.renderers.default = "notebook"

# Load data
df = pd.read_csv("att_2025-05-24_converted(in).csv")

# Force numeric
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")

# Keep only valid device locations
df_clean = df.dropna(subset=["device_lat", "device_lon"])

# Claimed location
his_lat = 39.32148
his_lon = -76.61831

# Build interactive plot — My DATA
fig = px.scatter(
    df_clean,
    x="device_lon",
    y="device_lat",
    hover_data={
        "conn_time_utc": True,
        "device_lat": True,
        "device_lon": True,
        "cell_id": True,
        "network_type": True
    },
    title="AT&T Device Location Points (May 24, 2025)",
)

# Explicitly style + AT&T data
fig.update_traces(
    marker=dict(size=8, color="blue", opacity=0.7),
    name="My AT&T Device Location Data",
    showlegend=True
)

# Add claimed location (his)
fig.add_scatter(
    x=[his_lon],
    y=[his_lat],
    mode="markers",
    marker=dict(size=12, color="red", symbol="x"),
    name="Claimed Location (His Coordinates)"
)

# Zoom to Baltimore
fig.update_layout(
    xaxis_title="Longitude",
    yaxis_title="Latitude",
    xaxis=dict(range=[-76.85, -76.55]),
    yaxis=dict(range=[39.25, 39.45]),
    legend=dict(
        title="Legend",
        yanchor="top",
        y=0.99,
        xanchor="left",
        x=0.01
    )
)

fig.show()

import pandas as pd
import plotly.express as px

# Load data
df = pd.read_csv("att_2025-05-24_converted(in).csv")

# Force numeric
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")

df_clean = df.dropna(subset=["device_lat", "device_lon"])

# Claimed location
his_lat = 39.32148
his_lon = -76.61831

# Map of Baltimore with AT&T points
fig = px.scatter_mapbox(
    df_clean,
    lat="device_lat",
    lon="device_lon",
    hover_data={
        "conn_time_utc": True,
        "device_lat": True,
        "device_lon": True,
        "cell_id": True
    },
    zoom=10,
    height=650,
    title="AT&T Device Location Data — Baltimore (May 24, 2025)"
)

# Style AT&T points
fig.update_traces(
    marker=dict(size=8, color="blue", opacity=0.7),
    name="My AT&T Device Location Data",
    showlegend=True
)

# Add claimed location
fig.add_scattermapbox(
    lat=[his_lat],
    lon=[his_lon],
    mode="markers",
    marker=dict(size=6, color="red"),
    name="Claimed Location (His Coordinates)"
)

# Map settings
fig.update_layout(
    mapbox_style="open-street-map",
    mapbox_center={"lat": 39.2904, "lon": -76.6122},  # Baltimore center
    legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)

fig.show()

/tmp/ipykernel_3487/3232317641.py:18: DeprecationWarning:

*scatter_mapbox* is deprecated! Use *scatter_map* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/

import numpy as np

# Haversine distance (meters)
def haversine(lat1, lon1, lat2, lon2):
    R = 6371000  # Earth radius in meters
    phi1 = np.radians(lat1)
    phi2 = np.radians(lat2)
    dphi = np.radians(lat2 - lat1)
    dlambda = np.radians(lon2 - lon1)

    a = np.sin(dphi/2)**2 + np.cos(phi1)*np.cos(phi2)*np.sin(dlambda/2)**2
    return 2 * R * np.arcsin(np.sqrt(a))

# Claimed coordinates
his_lat = 39.32148
his_lon = -76.61831

# Compute distance for every point
df_clean["distance_meters"] = haversine(
    df_clean["device_lat"],
    df_clean["device_lon"],
    his_lat,
    his_lon
)

# Identify nearest point
nearest_point = df_clean.loc[df_clean["distance_meters"].idxmin()]
nearest_point

/tmp/ipykernel_3487/869805969.py:19: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

row_id                     517.0
conn_date              5/24/2025
conn_time_utc           22:03:06
timing_advance              11.0
cell_id               18513672.0
network_type                  4G
tower_lat              39.316101
tower_lon             -76.631897
sector_orientation          31.0
device_lat             39.321331
device_lon            -76.623268
distance_meters       426.812755
Name: 399, dtype: object

import pandas as pd
import plotly.graph_objects as go
from math import radians, cos, sin, asin, sqrt

# -----------------------------
# Helper: Haversine distance (meters)
# -----------------------------
def haversine(lat1, lon1, lat2, lon2):
    R = 6371000  # Earth radius in meters
    lat1, lon1, lat2, lon2 = map(radians, [lat1, lon1, lat2, lon2])
    dlat = lat2 - lat1
    dlon = lon2 - lon1
    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
    return 2 * R * asin(sqrt(a))

# -----------------------------
# Load AT&T data
# -----------------------------
df = pd.read_csv("att_2025-05-24_converted(in).csv")

df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")

df_clean = df.dropna(subset=["device_lat", "device_lon"])

# -----------------------------
# Claimed location (his)
# -----------------------------
his_lat = 39.32148
his_lon = -76.61831

# -----------------------------
# Find nearest AT&T point
# -----------------------------
df_clean["distance_meters"] = df_clean.apply(
    lambda r: haversine(r["device_lat"], r["device_lon"], his_lat, his_lon),
    axis=1
)

nearest = df_clean.loc[df_clean["distance_meters"].idxmin()]

# -----------------------------
# Build map (ONLY TWO POINTS)
# -----------------------------
fig = go.Figure()

# Nearest AT&T point (Me)
fig.add_trace(go.Scattermapbox(
    lat=[nearest["device_lat"]],
    lon=[nearest["device_lon"]],
    mode="markers",
    marker=dict(size=16, color="blue"),
    name="Nearest AT&T Device Location",
    hovertext=(
        f"Device Location<br>"
        f"Lat: {nearest['device_lat']}<br>"
        f"Lon: {nearest['device_lon']}<br>"
        f"Time (UTC): {nearest['conn_time_utc']}<br>"
        f"Distance: {round(nearest['distance_meters'],1)} meters"
    )
))

# Claimed location (HIM)
fig.add_trace(go.Scattermapbox(
    lat=[his_lat],
    lon=[his_lon],
    mode="markers",
    marker=dict(size=16, color="red"),
    name="Claimed Location",
    hovertext=f"Claimed Location<br>Lat: {his_lat}<br>Lon: {his_lon}"
))

# -----------------------------
# Map settings — FORCE Baltimore
# -----------------------------
fig.update_layout(
    title="Claimed Location vs Nearest AT&T Device Location (May 24, 2025)",
    mapbox=dict(
        style="open-street-map",
        center=dict(lat=his_lat, lon=his_lon),
        zoom=16
    ),
    margin=dict(l=0, r=0, t=50, b=0),
    legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)

fig.show()

/tmp/ipykernel_3487/1199652861.py:35: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/tmp/ipykernel_3487/1199652861.py:48: DeprecationWarning:

*scattermapbox* is deprecated! Use *scattermap* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/

/tmp/ipykernel_3487/1199652861.py:64: DeprecationWarning:

*scattermapbox* is deprecated! Use *scattermap* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/

import pandas as pd
import plotly.express as px
import plotly.io as pio

pio.renderers.default = "notebook"  

# -----------------------------
# 1) Load CSV
# -----------------------------
df = pd.read_csv("att_2025-05-24_converted(in).csv")

# Force numeric 
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
df["row_id"] = pd.to_numeric(df["row_id"], errors="coerce")

# Keep only valid device points
df_clean = df.dropna(subset=["device_lat", "device_lon", "row_id"]).copy()

# -----------------------------
# 2) Claimed location (his)
# -----------------------------
his_lat = 39.32148
his_lon = -76.61831

# -----------------------------
# 3) Filter rows 517–525
# -----------------------------
subset = df_clean[(df_clean["row_id"] >= 517) & (df_clean["row_id"] <= 525)].copy()

# Add a label so Plotly can color the two groups
subset["point_type"] = "AT&T device locations (rows 517–525)"

claimed = pd.DataFrame([{
    "row_id": None,
    "conn_time_utc": None,
    "device_lat": his_lat,
    "device_lon": his_lon,
    "point_type": "Claimed location (his coordinates)"
}])

plot_df = pd.concat([subset, claimed], ignore_index=True)

# -----------------------------
# 4) Map plot (Baltimore zoom)
# -----------------------------
fig = px.scatter_map(
    plot_df,
    lat="device_lat",
    lon="device_lon",
    color="point_type",
    hover_data={
        "row_id": True,
        "conn_time_utc": True,
        "device_lat": ":.6f",
        "device_lon": ":.6f"
    },
    zoom=12,
    center={"lat": his_lat, "lon": his_lon},
    map_style="open-street-map",
    title="Claimed Location vs AT&T Device Locations (Rows 517–525) — May 24, 2025"
)

# BIG, CLEAR DOTS
fig.update_traces(marker=dict(size=18, opacity=0.9))

# Clean legend placement
fig.update_layout(
    legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)

fig.show()

/tmp/ipykernel_3487/2737704668.py:42: FutureWarning:

The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.